package com.mrcoder.sbmexcel.controller;

import com.alibaba.fastjson.JSON;
import com.mrcoder.sbmexcel.service.ExcelService;
import com.mrcoder.sbmexcel.utils.excel.ImportData;
import com.mrcoder.sbmexcel.utils.excel.RobotShift;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@RestController
public class ExcelController {

    @Autowired
    private ExcelService excelService;
    final String[] header = new String[]{"Currency", "Game", "Room", "Date", "Time", "minCount", "count", "fullTable", "seatPercentage"};

    @RequestMapping(value = "/excelImport", method = RequestMethod.POST)
    public String uploadImg(@RequestParam("file") MultipartFile file, HttpServletRequest request) throws IOException {
        String contentType = file.getContentType();
        String fileName = file.getOriginalFilename();
        if (file.isEmpty()) {
            return "文件为空！";
        }
        final InputStream in = file.getInputStream();
        if (fileName.contains("csv")) {
            InputStreamReader inputStreamReader = new InputStreamReader(in, "GBK");
            final Reader reader = new BufferedReader(inputStreamReader);
            CSVFormat format = CSVFormat.DEFAULT.withHeader(header).withSkipHeaderRecord();
            final CSVParser csvParser = format.parse(reader);
            try {
                //指定csv的标题头 设置跳过Header
                //将每一行记录存入list中
                List<CSVRecord> list = csvParser.getRecords();
                for (CSVRecord record : list) {
                    Integer size = record.size();
                    RobotShift data = new RobotShift();
                    String currency = record.get("Currency");
                    if (currency.equals("") || currency == null) {
                        System.out.println("excel row is last");
                        return "success import";
                    }
                    String game = record.get("Game");
                    String room = record.get("Room");
                    String date = record.get("Date");
                    String time = record.get("Time");
                    String taskName = game.concat(currency).concat(room);
                    Integer minCount = Integer.valueOf((record.get(5)));
                    Integer shiftCount = Integer.valueOf((record.get(6)));
                    Integer fullTable = Integer.valueOf((record.get(7)));
                    Integer seatPercentage = Integer.valueOf((record.get(8)));
                    ;
//                   record.get(5);
//                    System.out.println(record.getRecordNumber()
//                            + ":" + record.get("name")
//                            + ":" + record.get("sex")
//                            + ":" + record.get("age") + ":" + record.get("birthday")+ record.get("身高"));
                }

                return "success import";
            } catch (Exception e) {
            } finally {
                reader.close();
                csvParser.close();
            }
        }
        try {
            //根据路径获取这个操作excel的实例
            Workbook wb = WorkbookFactory.create(in);
            Sheet sheet = wb.getSheetAt(0);
            //实体类集合
            List<ImportData> importDatas = new ArrayList<>();
            Row row = null;
            //循环sesheet页中数据从第二行开始，第一行是标题
            for (int i = 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                //获取每一行数据
                row = sheet.getRow(i);
                if (row == null) {
                    return JSON.toJSONString(importDatas);
                }
                ImportData data = new ImportData();
                data.setId(Integer.valueOf((int) row.getCell(0).getNumericCellValue()));
                data.setName(row.getCell(1).getStringCellValue());
                SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd");
                data.setCreateDate(df.parse(df.format(HSSFDateUtil.getJavaDate(row.getCell(2).getNumericCellValue()))));
                data.setAge(Integer.valueOf((int) row.getCell(3).getNumericCellValue()));
                importDatas.add(data);
            }
            //循环展示导入的数据，实际应用中应该校验并存入数据库
            for (ImportData imdata : importDatas) {
                SimpleDateFormat df = new SimpleDateFormat("yyyy/MM/dd hh:mm:ss");
                System.out.println("ID:" + imdata.getId() + " name:" + imdata.getName() + " createDate:" + df.format(imdata.getCreateDate()) + " age:" + imdata.getAge());
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "导入成功!";

    }


    @PostMapping("/importExcel")
    public int importExcel(HttpServletRequest request) {
        return excelService.importExcel(request);
    }


    @GetMapping("/exportExcel")
    public void exportExcel(HttpServletResponse response) {
        excelService.exportExcel(response);
    }

}
